package foodmaterial.excel;

import foodmaterial.action.FoodMaterialAction;
import foodmaterial.action.OrderAction;
import foodmaterial.dao.*;
import foodmaterial.dto.*;
import foodmaterial.entity.*;
import foodmaterial.service.CustomerService;
import foodmaterial.service.FoodMaterialService;
import foodmaterial.service.OrderService;
import foodmaterial.service.impl.CustomerServiceImpl;
import foodmaterial.utils.*;
import foodmaterial.utils.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

import static foodmaterial.excel.EntityDefine.*;

@Component
public class Excel {
    @Autowired
    private CustomerDao customerDao ;

    @Autowired
    private FoodMaterialDao foodMaterialDao ;

    @Autowired
    private PurchaseDao purchaseDao ;

    @Autowired
    private FinancialDao financialDao ;

    @Autowired
    private InitDao initDao ;

    @Autowired
    private OtherDao otherDao;

    @Autowired
    private StockDao stockDao ;

    @Autowired
    private SnowflakeWorker worker ;

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream
     * @param fileType
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) {
        Workbook workbook = null;
        try {
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            FileTools.close(workbook);
            throw new RuntimeException(e);
        } finally {
            FileTools.close(inputStream);
        }
        return workbook;
    }

    public Workbook getWorkbook(String templateName) {
        FileInputStream in = null ;
        try {
            in = new FileInputStream(initDao.selectSysParamsByCode(Constants.EXCEL_TEMPLATE_PATH).getParamValue() + templateName) ;
            int pos = templateName.lastIndexOf('.') ;
            String suffix = templateName.substring(pos +1) ;
            return getWorkbook(in, suffix) ;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            FileTools.close(in);
            throw new RuntimeException(e) ;
        }
    }

    private static String convertCellValueToString(Cell cell,int doubleFormat) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                if (doubleFormat == 1) {//处理电话
                    DecimalFormat df = new DecimalFormat("0");
                    returnValue = df.format(doubleValue);
                } else returnValue = Double.toString(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    private static Object createEntity(Class<?> entityClz) {
        try {
            return entityClz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        }
    }

    private static String setter(Object target, EntityDefine.EntityDesc entityDesc, Object value) {
        if (StringUtils.isEmpty(value) && entityDesc.isCheck()) return "";
        try {
            Method m = entityDesc.getSet(target.getClass()) ;
            if (entityDesc.getParam().getName().equals("java.lang.String")) m.invoke(target,(String)value) ;
            else if (entityDesc.getParam().getName().equals("java.lang.Long"))
                m.invoke(target,(Long)value) ;
            else if (entityDesc.getParam().getName().equals("java.lang.Double")) {
                if (StringUtils.isEmpty(value)) value = entityDesc.getDefaultValue();
                m.invoke(target, Double.valueOf(value.toString()));
            } else if (entityDesc.getParam().getName().equals("java.lang.Integer")) {
                if (StringUtils.isEmpty(value)) value = entityDesc.getDefaultValue();
                String temp = value.toString() ;
                if (temp.indexOf('.') != -1) {
                    temp = temp.substring(0,temp.indexOf('.')) ;
                }
                m.invoke(target, Integer.valueOf(temp));
            } else if (entityDesc.getParam().getName().equals("java.math.BigDecimal")) {
                if (StringUtils.isEmpty(value)) value = entityDesc.getDefaultValue();
                m.invoke(target, new BigDecimal(value.toString()));
            }
        } catch (IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        }
        return null ;
    }

    private static String getter(Object target, EntityDefine.EntityDesc entityDesc) {
        try {
            Method m = entityDesc.getGet(target.getClass()) ;
            Object result = m.invoke(target,null) ;
            if (result != null) {
                if (entityDesc.isBoolean()) return entityDesc.getBooleanShowValue(result) ;
                return entityDesc.executeFormatter(result).toString();
            }
        } catch (IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        }
        return "" ;
    }

    private Map<String,Long> initAreaValue(List<SupplyArea> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (SupplyArea sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initRouterValue(List<RouterInfo> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (RouterInfo sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initSupplyTypeValue(List<SupplyType> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (SupplyType sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initSupplyTeamValue(List<SupplyTeam> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (SupplyTeam sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Set<String> initCustomerValue(List<CustomerInfo> list) {
        Set<String> customerSet = new HashSet<>() ;
        for (CustomerInfo sa : list) {
            customerSet.add(sa.getName()) ;
        }
        return customerSet ;
    }

    private Map<String,Long> initFoodMaterialTypeValue(List<FoodMaterialType> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (FoodMaterialType sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initFoodMaterialUnitValue(List<FoodMaterialUnit> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (FoodMaterialUnit sa : list) {
            map.put(sa.getName(),sa.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initFoodMaterialHashMapValue(List<FoodMaterial> list) {
        Map<String,Long> map = new HashMap<>() ;
        for (FoodMaterial fa : list) {
            map.put(fa.getName(),fa.getId()) ;
        }
        return map ;
    }

    private Set<String> initFoodMaterialValue(List<FoodMaterial> list) {
        Set<String> set = new HashSet<>() ;
        for (FoodMaterial fa : list) {
            set.add(fa.getName()) ;
        }
        return set ;
    }

    private Set<String> initCustomerMealsValue(List<MealsType> list) {
        Set<String> set = new HashSet<>() ;
        for (MealsType sa : list) {
            set.add(sa.getCustomerName() + sa.getMealsTypeName()) ;
        }
        return set ;
    }

    private Map<String,Long> initMealsTypeValue(List<MealsTypeName> list) {
        Map<String,Long> map = new HashMap<>();
        for (MealsTypeName mtn : list) {
            map.put(mtn.getName(),mtn.getId()) ;
        }
        return map ;
    }

    private Map<String,Long> initCustomerMapValue(List<CustomerInfo> list) {
        Map<String,Long> customers = new HashMap<>() ;
        for (CustomerInfo sa : list) {
            customers.put(sa.getName(),sa.getId()) ;
        }
        return customers ;
    }

    private Map<String,Long> initFoodMapValue(List<FoodMaterial> list) {
        Map<String,Long> customers = new HashMap<>() ;
        for (FoodMaterial sa : list) {
            customers.put(sa.getName(),sa.getId()) ;
        }
        return customers ;
    }

    private Map<String,Long> initSupplierMapValue(List<Supplier> list) {
        Map<String,Long> customers = new HashMap<>() ;
        for (Supplier sa : list) {
            customers.put(sa.getName(),sa.getId()) ;
        }
        return customers ;
    }

    private void addValue(Map<String,Long> old,Map<String,Long> add,String key) {
        Long id = worker.nextId() ;
        old.put(key,id) ;
        add.put(key,id) ;
    }

    @Transactional(rollbackFor = Exception.class)
    public String uploadCustomerExcel(Workbook workbook) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        SupplyArea supplyArea = new SupplyArea() ;
        Map<String,Long> areaMap = initAreaValue(customerDao.selectSupplyArea(supplyArea)) ;
        Map<String,Long> areaMapAdd = new HashMap<>() ;
        RouterInfo routerInfo = new RouterInfo() ;
        Map<String,Long> routerMap = initRouterValue(customerDao.selectRouterInfo(routerInfo)) ;
        Map<String,Long> routerMapAdd = new HashMap<>();
        SupplyTeam supplyTeam = new SupplyTeam() ;
        Map<String,Long> teamMap = initSupplyTeamValue(customerDao.selectSupplyTeam(supplyTeam)) ;
        Map<String,Long> teamMapAdd = new HashMap<>() ;
        SupplyType supplyType = new SupplyType() ;
        Map<String,Long> typeMap = initSupplyTypeValue(customerDao.selectSupplyType(supplyType));
        Map<String,Long> typeMapAdd = new HashMap<>() ;
        Set<String> customerSet = initCustomerValue(customerDao.selectCustomerInfo(new CustomerInfoDto())) ;
        List<CustomerInfo> customerInfos = new ArrayList<>() ;
        CustomerSeq cs = otherDao.selectCustomerSeq();
        Long defaultId = new Long(-1);
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                //System.out.println("解析Excel失败，在第一行没有读取到任何数据！");
                return "解析Excel失败，在第一行没有读取到任何数据！";
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                CustomerInfo entity = new CustomerInfo();
                for (EntityDefine.EntityDesc entityDesc : CustomerDefine) {
                    String value = null ;
                    if (entityDesc.getSetField().equals("LiaisonTel") ||
                        entityDesc.getSetField().equals("AreaMasterTel"))
                        value = convertCellValueToString(row.getCell(entityDesc.getPos()),1) ;
                    else value = convertCellValueToString(row.getCell(entityDesc.getPos()),0) ;
                    if (entityDesc.isCheck() && value == null) {
                        return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                    }
                    if (value != null) value = value.trim();
                    if (entityDesc.setField.equals("AreaId")) {
                        if (areaMap.containsKey(value) == false) {
                            addValue(areaMap,areaMapAdd,value) ;
                        }
                        String result = setter(entity, entityDesc, areaMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("RouterId")) {
                        if (routerMap.containsKey(value) == false) {
                            addValue(routerMap,routerMapAdd,value) ;
                        }
                        String result = setter(entity, entityDesc, routerMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("SupplyTypeId")) {
                        if (typeMap.containsKey(value) == false) {
                            addValue(typeMap,typeMapAdd,value) ;
                        }
                        String result = setter(entity, entityDesc, typeMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("SupplyTeamId")) {
                        if (teamMap.containsKey(value) == false) {
                            addValue(teamMap,teamMapAdd,value) ;
                        }
                        String result = setter(entity, entityDesc, teamMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("ShowOrder")) {
                        if (value == null) value = "0";
                        String result = setter(entity, entityDesc, value);
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else {
                        setter(entity, entityDesc, value);
                    }
                }
                if (customerSet.contains(entity.getName())) return entity.getName() + " 已经上传过了";
                entity.setPassword(PasswordUtil.encodePassword(Constants.DEFAULT_PASSWORD));
                entity.setId(worker.nextId());
                entity.setCode(CustomerServiceImpl.getCustomerSeq(cs));
                entity.setShowOrder(0);
                entity.setDistributionSorting(Boolean.FALSE);
                customerInfos.add(entity) ;
            }
        }
        customerDao.batchInsertCustomerInfo(customerInfos) ;
        otherDao.updateCustomerSeq(cs);
        areaMapAdd.forEach((k,v) -> {
            SupplyArea sa = new SupplyArea() ;
            sa.setId(v);
            sa.setName(k);
            customerDao.insertSupplyArea(sa) ;
        });
        routerMapAdd.forEach((k,v) -> {
            RouterInfo ri = new RouterInfo();
            ri.setId(v);
            ri.setName(k);
            customerDao.insertRouterInfo(ri) ;
        });
        typeMapAdd.forEach((k,v) -> {
            SupplyType st = new SupplyType() ;
            st.setId(v);
            st.setName(k);
            st.setAheadTime(0);
            customerDao.insertSupplyType(st) ;
        });
        teamMapAdd.forEach((k,v) -> {
            SupplyTeam st = new SupplyTeam();
            st.setId(v);
            st.setName(k);
            customerDao.insertSupplyTeam(st) ;
        });
        return null ;
    }

    @Transactional(rollbackFor = Exception.class)
    public String uploadCustomerMealsExcel(Workbook workbook) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        Map<String,Long> mealsTypeMap = initMealsTypeValue(customerDao.selectMealsTypeName(new MealsTypeName()));
        Set<String> mealsSet = initCustomerMealsValue(customerDao.selectMealsType(new MealsTypeDto())) ;
        Map<String,Long> customers = initCustomerMapValue(customerDao.selectCustomerInfo(new CustomerInfoDto()));
        List<MealsType> mealsTypes = new ArrayList<>() ;
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                return "解析Excel失败，在第一行没有读取到任何数据！";
            }

            // 解析每一行的数据，构造数据对象
            StringBuilder sb = new StringBuilder();
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                MealsType entity = new MealsType();
                sb.setLength(0);
                for (EntityDefine.EntityDesc entityDesc : CustomerMealsDefine) {
                    String value = convertCellValueToString(row.getCell(entityDesc.getPos()),0) ;
                    if (entityDesc.isCheck() && value == null) {
                        return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                    }
                    if (value != null) value = value.trim();
                    if (entityDesc.setField.equals("CustomerId")) {
                        String result = setter(entity, entityDesc, customers.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                        sb.append(value);
                    } else if (entityDesc.setField.equals("MealsTypeNameId")) {
                        String result = setter(entity, entityDesc, mealsTypeMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                        sb.append(value);
                    } else {
                        setter(entity, entityDesc, value);
                    }
                }
                if (mealsSet.contains(sb.toString())) return  sb.toString() + " 已经上传过了";
                entity.setId(worker.nextId());
                mealsTypes.add(entity) ;
            }
        }
        customerDao.batchInsertMealsType(mealsTypes) ;
        return null ;
    }

    @Transactional(rollbackFor = Exception.class)
    @CacheEvict(cacheNames={FoodMaterialService.FOOD_MATERIAL_TYPE_CACHE},key = "#db")
    public String uploadFoodMaterialExcel(Workbook workbook,String db,boolean originalProcess) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        FoodMaterialTypeDto foodMaterialType = new FoodMaterialTypeDto() ;
        FoodMaterialUnit foodMaterialUnit = new FoodMaterialUnit() ;
        Map<String,Long> typeMap = initFoodMaterialTypeValue(foodMaterialDao.selectFoodMaterialType(foodMaterialType)) ;
        Map<String,Long> typeMapAdd = new HashMap<>() ;
        Map<String,Long> unitMap = initFoodMaterialUnitValue(foodMaterialDao.selectFoodMaterialUnit(foodMaterialUnit)) ;
        Map<String,Long> unitMapAdd = new HashMap<>() ;
        FoodMaterialDto dto = new FoodMaterialDto() ;
        dto.setOriginalProcess(false);
        Map<String,Long> foodMap = initFoodMaterialHashMapValue(foodMaterialDao.selectFoodMaterial(dto));
        dto.setOriginalProcess(true);
        Set<String> foodProcessSet = originalProcess ?
                initFoodMaterialValue(foodMaterialDao.selectFoodMaterial(dto)) : null;
        // 解析sheet
        List<FoodMaterial> foodMaterials = new ArrayList<>();
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                return "解析Excel失败，在第一行没有读取到任何数据！";
            }
            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;
                FoodMaterial entity = new FoodMaterial();
                for (EntityDefine.EntityDesc entityDesc : FoodMaterialDefine) {
                    if (originalProcess == false) {
                        if (entityDesc.setField.equals("OriginalProcessRate") ||
                                entityDesc.setField.equals("OriginalType"))
                            continue ;
                    }
                    String value = convertCellValueToString(row.getCell(entityDesc.getPos()),0) ;
                    if (entityDesc.isCheck() && value == null) {
                        return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                    }
                    if (value != null) value = value.trim();
                    if (entityDesc.setField.equals("TypeId")) {
                        if (typeMap.containsKey(value) == false) {
                            addValue(typeMap, typeMapAdd, value);
                        }
                        String result = setter(entity, entityDesc, typeMap.get(value));
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    }
                    else if (entityDesc.setField.equals("UnitName")) {
                        if (unitMap.containsKey(value) == false) {
                            addValue(unitMap,unitMapAdd,value) ;
                        }
                        String result = setter(entity, entityDesc, value);
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("OriginalType")) {
                        Long originalId = foodMap.get(value) ;
                        if (originalId == null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                        String result = setter(entity, entityDesc, originalId);
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else {
                        setter(entity, entityDesc, value);
                    }
                }
                if (originalProcess == false && foodMap.containsKey(entity.getName())) return entity.getName() + " 已经上传过了" ;
                if (originalProcess) {
                   if (foodProcessSet.contains(entity.getName())) return entity.getName() + " 已经上传过了" ;
                }
                entity.setId(worker.nextId());
                entity.setProcessPurchase(Boolean.FALSE);
                if (originalProcess == false) {
                    entity.setOriginalType(entity.getId());
                    entity.setOriginalProcess(Boolean.FALSE);
                    entity.setOriginalProcessRate(100.);
                } else {
                    entity.setOriginalProcess(Boolean.TRUE);
                }
                foodMaterials.add(entity) ;
            }
        }
        foodMaterialDao.batchInsertFoodMaterial(foodMaterials) ;
        typeMapAdd.forEach((k,v) -> {
            FoodMaterialType st = new FoodMaterialType() ;
            st.setId(v);
            st.setName(k);
            st.setIsCondiment(false);
            foodMaterialDao.insertFoodMaterialType(st) ;
        });
        unitMapAdd.forEach((k,v) -> {
            FoodMaterialUnit unit = new FoodMaterialUnit() ;
            unit.setId(v);
            unit.setName(k);
            foodMaterialDao.insertFoodMaterialUnit(unit) ;
        });
        return null ;
    }

    public String uploadFoodMaterialSupplyExcel(Workbook workbook,boolean originalProcess,FoodMaterialService service) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        FoodMaterialDto dto = new FoodMaterialDto() ;
        dto.setOriginalProcess(originalProcess);
        Map<String,Long> foodMap = initFoodMaterialHashMapValue(foodMaterialDao.selectFoodMaterial(dto));
        FoodMaterialAction.Supply supply = new FoodMaterialAction.Supply() ;
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                return "解析Excel失败，在第一行没有读取到任何数据！";
            }
            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 2;
            Row row = sheet.getRow(rowStart);
            if (row == null) {
                return "请在excel中输入供应开始日期" ;
            }
            String value = convertCellValueToString(row.getCell(0),0) ;
            if (value == null) {
                return "请在excel中输入供应开始日期" ;
            }
            Date beginDate = DateUtil.parse(value,Constants.DEFAULT_DATE_FORMAT) ;
            rowStart = firstRowNum + 4;
            row = sheet.getRow(rowStart);
            if (row == null) {
                return "请在excel中输入供应截至日期" ;
            }
            value = convertCellValueToString(row.getCell(0),0) ;
            if (value == null) {
                return "请在excel中输入供应截至日期" ;
            }
            Date endDate = DateUtil.parse(value,Constants.DEFAULT_DATE_FORMAT) ;
            rowStart = firstRowNum + 6;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            List<Long> foodMaterialIds = new ArrayList<>() ;
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                row = sheet.getRow(rowNum);
                if (row == null) continue;
                EntityDefine.EntityDesc entityDesc = FoodMaterialSupplyDefine[0] ;
                value = convertCellValueToString(row.getCell(entityDesc.getPos()),0) ;
                if (entityDesc.isCheck() && value == null) {
                    return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                }
                value = value.trim() ;
                if (foodMap.containsKey(value) == false) {
                    return rowNum + "行：【" + value + "】不存在" ;
                }
                foodMaterialIds.add(foodMap.get(value)) ;
            }
            supply.setOriginalProcess(originalProcess);
            supply.setFoodMaterialId(foodMaterialIds);
            supply.setBeginDate(beginDate);
            supply.setEndDate(endDate);
            return service.addFoodMaterialSupply(supply) ;
        }
        return null ;
    }

    /**
     * 将数据转换成行
     * @param data 源数据
     * @param row 行对象
     * @return
     */
    private static void convertDataToRow(Object data, Row row, EntityDefine.EntityDesc[] define){
        int cellNum = 0;
        Cell cell;
        for (EntityDefine.EntityDesc entityDesc : define) {
            cell = row.createCell(cellNum++);
            cell.setCellValue(getter(data,entityDesc));
        }
    }

    private static void convertDataToRow(Object data, Row row, int rowNo,EntityDefine.EntityDesc[] define){
        int cellNum = 0;
        Cell cell = row.createCell(cellNum++);
        cell.setCellValue(rowNo);
        for (EntityDefine.EntityDesc entityDesc : define) {
            cell = row.createCell(cellNum++);
            cell.setCellValue(getter(data,entityDesc));
        }
    }

    public void downloadCustomerExcel(String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        CustomerInfoDto dto = new CustomerInfoDto();
        List<CustomerInfo> data = customerDao.selectCustomerInfo(dto) ;
        //构建每行的数据内容
        int rowNum = 2;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            for (CustomerInfo c : data) {
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(c, row,CustomerDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    public void downloadFoodMaterialExcel(String templateName, OutputStream out,boolean originalProcess) {
        Workbook workbook = getWorkbook(templateName) ;
        FoodMaterialDto dto = new FoodMaterialDto();
        dto.setOriginalProcess(originalProcess);
        List<FoodMaterial> data = foodMaterialDao.selectFoodMaterial(dto) ;
        //构建每行的数据内容
        int rowNum = 2;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            for (FoodMaterial food : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                if (originalProcess) convertDataToRow(food, row,ProcessFoodMaterialDownloadDefine);
                else convertDataToRow(food, row,FoodMaterialDownloadDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    //采购计划
    public void downloadPurchasePlanExcel(PurchasePlanDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            List<PurchaseBillDetails> data = dto.getOriginalProcess().booleanValue() == false ?
                    purchaseDao.selectPlan(dto) : purchaseDao.selectProcessPlan(dto);
            String date = dto.getBeginDistributionDate() + "至" + dto.getEndDistributionDate() ;
            sheet.getRow(0).getCell(0).setCellValue(sheet.getRow(0).getCell(0).getStringCellValue() + "-" + getOriginalProcessName(dto.getOriginalProcess()));
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            //构建每行的数据内容
            int rowNum = 3;
            for (PurchaseBillDetails detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,PurchasePlanDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    private String getOriginalProcessName(Boolean bool) {
        if (bool == null) return "";
        if (bool.booleanValue()) return "净菜";
        else return "毛菜";
    }

    //采购汇总
    public void downloadPurchaseSummaryExcel(PurchaseSummaryDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<PurchaseBillDetails> data = purchaseDao.selectPurchaseSummary(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginPurchaseDate() + "至" + dto.getEndPurchaseDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + getOriginalProcessName(dto.getOriginalProcess()));
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getSupplierName());
            sheet.getRow(2).getCell(3).setCellValue(sheet.getRow(2).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            //构建每行的数据内容
            int rowNum = 4;
            for (PurchaseBillDetails detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,PurchaseSummaryDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    //下载按线路配货汇总
    public void downloadDistributionSummaryExcel(SupplySummaryBySupplyDto dto,
         String templateName, OutputStream out, OrderService orderService) {
        Workbook workbook = getWorkbook(templateName) ;
        OrderAction.SupplySummary data = orderService.querySupplySummaryByRouter(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginDistributionDate() + "至" + dto.getEndDistributionDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getRouterName());
            sheet.getRow(3).getCell(0).setCellValue(sheet.getRow(3).getCell(0).getStringCellValue() + dto.getFoodMaterialTypeName());
            sheet.getRow(4).getCell(0).setCellValue(sheet.getRow(4).getCell(0).getStringCellValue() + dto.getMealsTypeName());
            //构建每行的数据内容
            int rowNum = 5;
            int cellNum = 0 ;
            Row row = sheet.createRow(rowNum++);
            row.createCell(cellNum++).setCellValue("食材");
            for (String h : data.getHeader()) {
                row.createCell(cellNum++).setCellValue(h);
            }
            row.createCell(cellNum++).setCellValue("汇总");
            for (Map<String,Object> detail : data.getData()) {
                cellNum = 0 ;
                row = sheet.createRow(rowNum++);
                row.createCell(cellNum++).setCellValue(detail.get("name").toString());
                for (String h : data.getHeader()) {
                    Object v = detail.get(h) ;
                    row.createCell(cellNum++).setCellValue(v != null?v.toString():"");
                }
                row.createCell(cellNum++).setCellValue(detail.get("total").toString());
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    public void downloadRecipeDistributionSummaryExcel(SupplySummaryBySupplyDto dto,
                                                 String templateName, OutputStream out, OrderService orderService) {
        Workbook workbook = getWorkbook(templateName) ;
        dto.setRecipe(Boolean.TRUE);
        OrderAction.SupplySummary data = orderService.querySupplySummaryByRouter(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginDistributionDate() + "至" + dto.getEndDistributionDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(3).getCell(0).setCellValue(sheet.getRow(3).getCell(0).getStringCellValue() + dto.getMealsTypeName());
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getRouterName());
            //构建每行的数据内容
            int rowNum = 5;
            int cellNum = 0 ;
            for (Map<String,Object> detail : data.getData()) {
                Row row = sheet.createRow(rowNum++);
                cellNum = 0 ;
                row.createCell(cellNum++).setCellValue(detail.get("distributionDate").toString());
                row.createCell(cellNum++).setCellValue(detail.get("ordersId").toString());
                row.createCell(cellNum++).setCellValue(detail.get("customerName").toString());
                row.createCell(cellNum++).setCellValue(detail.get("mealsTypeName").toString());
                row.createCell(cellNum++).setCellValue(detail.get("totalAmount").toString());
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    //仓库报损报溢汇总
    public void downloadProfitAndLossExcel(StockBillDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;

        List<StockBillDetail> data = financialDao.selectStockDetail(dto) ;
        //构建每行的数据内容
        int rowNum = 3;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(0).getCell(0).setCellValue(sheet.getRow(0).getCell(0).getStringCellValue() +
                    "-" + getOriginalProcessName(dto.getOriginalProcess()));
            String date = dto.getBeginStockDate() + "至" + dto.getEndStockDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            for (StockBillDetail detail : data) {
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,ProfitAndLossDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    //下载采购对账单
    public void downloadPurchaseCheckExcel(PurchaseBillDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<PurchaseBill> data = financialDao.selectPurchaseBill(dto) ;
        //构建每行的数据内容
        int rowNum = 3;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginPurchaseDate() + "至" + dto.getEndPurchaseDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getSupplierName());
            BigDecimal total = new BigDecimal("0.0");
            for (PurchaseBill detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,PurchaseCheckDefine);
                total = Arithmetic.add(total,detail.getAmountOfMoney(),2) ;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    //财务采购汇总（带金额）
    public void downloadFinancialPurchaseSummaryExcel(PurchaseBillDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<PurchaseBillDetails> data = financialDao.selectPurchaseSummary(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(0).getCell(0).setCellValue(sheet.getRow(0).getCell(0).getStringCellValue() +
                    "-" + getOriginalProcessName(dto.getOriginalProcess()));
            String date = dto.getBeginPurchaseDate() + "至" + dto.getEndPurchaseDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            //构建每行的数据内容
            int rowNum = 3;
            for (PurchaseBillDetails detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,FinancialPurchaseSummaryDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    public void downloadCustomerBillingDetailedListExcel(CustomerBillingDetailedListDto dto,
        String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<OrdersDetail> data = financialDao.selectBillingDetailedList(dto) ;
        //构建每行的数据内容
        int rowNum = 5;
        BigDecimal total = new BigDecimal("0.00") ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginSupplyDate() + "至" + dto.getEndSupplyDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(4).setCellValue(sheet.getRow(1).getCell(4).getStringCellValue() + dto.getCustomerName());
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getMealsTypeName());
            sheet.getRow(3).getCell(0).setCellValue(sheet.getRow(3).getCell(0).getStringCellValue() + dto.getFoodMaterialTypeName());
            for (OrdersDetail detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,CustomerBillingDetailedListDefine);
                total = Arithmetic.add(total,detail.getTotal(),2) ;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计金额：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadRecipeCustomerBillingDetailedListExcel(CustomerBillingDetailedListDto dto,
                                                         String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<OrdersRecipe> data = financialDao.selectRecipeBillingDetailedList(dto) ;
        //构建每行的数据内容
        int rowNum = 5;
        BigDecimal total = new BigDecimal("0.00") ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginSupplyDate() + "至" + dto.getEndSupplyDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getCustomerName());
            sheet.getRow(3).getCell(0).setCellValue(sheet.getRow(3).getCell(0).getStringCellValue() + dto.getMealsTypeName());
            for (OrdersRecipe detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,RecipeCustomerBillingDetailedListDefine);
                total = Arithmetic.add(total,detail.getTotal(),2) ;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计金额：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadCustomerCheckExcel(CustomerBillingDetailedListDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<Orders> data = financialDao.selectCustomerCheck(dto) ;
        //构建每行的数据内容
        int rowNum = 5;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + dto.getCustomerName());
            sheet.getRow(1).getCell(2).setCellValue(sheet.getRow(1).getCell(2).getStringCellValue() + dto.getCompanyName());
            String date = dto.getBeginSupplyDate() + "至" + dto.getEndSupplyDate() ;
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + date);
            sheet.getRow(3).getCell(0).setCellValue(sheet.getRow(3).getCell(0).getStringCellValue() + dto.getMealsTypeName());
            int currentRow = 1 ;
            BigDecimal total = new BigDecimal("0.00") ;
            for (Orders detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,currentRow,CustomerCheckDefine);
                total = Arithmetic.add(total,detail.getAmountOfMoney(),2) ;
                currentRow++;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadStockExcel(FoodMaterialDto dto,String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<FoodMaterial> data = foodMaterialDao.selectFoodMaterial(dto) ;
        int rowNum = 3;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(0).getCell(0).setCellValue(sheet.getRow(0).getCell(0).getStringCellValue() +
                    "-" + getOriginalProcessName(dto.getOriginalProcess()));
            for (FoodMaterial detail : data) {
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,StockDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadSaleSummaryExcel(SaleSummaryDto dto, String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;

        List<OrdersDetail> data = financialDao.selectSaleSummary(dto) ;
        //构建每行的数据内容
        int rowNum = 3;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(0).getCell(0).setCellValue(sheet.getRow(0).getCell(0).getStringCellValue() +
                    "-" + getOriginalProcessName(dto.getOriginalProcess()));
            String date = dto.getBeginSupplyDate() + "至" + dto.getEndSupplyDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            BigDecimal total = new BigDecimal("0.00") ;
            for (OrdersDetail detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,SaleSummaryDefine);
                total = Arithmetic.add(total,detail.getTotal(),2) ;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadRecipeSaleSummaryExcel(OrdersRecipeDto dto, String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;
        List<OrdersRecipe> data = financialDao.selectRecipeSaleSummary(dto) ;
        //构建每行的数据内容
        int rowNum = 4;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginSupplyDate() + "至" + dto.getEndSupplyDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + dto.getRecipeTypeName());
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + date);
            BigDecimal total = new BigDecimal("0.00") ;
            for (OrdersRecipe detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,RecipeSaleSummaryDefine);
                total = Arithmetic.add(total,detail.getTotal(),2) ;
            }
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue("合计：" + total);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    //下载按线路餐别客户食材的清单
    public void downloadOrderDetailsByDistributionDateAndRouterExcel(DistributionDateAndRouterDto dto,
                 String templateName, OutputStream out, OrderService orderService) {
        Workbook workbook = getWorkbook(templateName) ;
        OrderAction.SupplySummary data = orderService.queryOrderDetailsByDistributionDateAndRouter(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + dto.getDistributionDate());
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getRouterName());
            //构建每行的数据内容
            int rowNum = 3;
            int cellNum = 0 ;
            Row row = sheet.createRow(rowNum++);
            row.createCell(cellNum++).setCellValue("餐别");
            row.createCell(cellNum++).setCellValue("食材");
            for (String h : data.getHeader()) {
                row.createCell(cellNum++).setCellValue(h);
            }
            for (Map<String,Object> detail : data.getData()) {
                cellNum = 0 ;
                row = sheet.createRow(rowNum++);
                row.createCell(cellNum++).setCellValue(detail.get("mealsTypeName").toString());
                row.createCell(cellNum++).setCellValue(detail.get("foodMaterialName").toString());
                for (String h : data.getHeader()) {
                    Object v = detail.get(h) ;
                    row.createCell(cellNum++).setCellValue(v != null?v.toString():"");
                }
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    public String uploadCustomerSupplierExcel(Workbook workbook, CustomerService service) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        Map<String,Long> customers = initCustomerMapValue(customerDao.selectCustomerInfo(new CustomerInfoDto()));
        Map<String,Long> foods = initFoodMapValue(foodMaterialDao.selectFoodMaterial(new FoodMaterialDto()));
        Map<String,Long> suppliers = initSupplierMapValue(purchaseDao.selectSupplier(new SupplierDto()));
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(0);
            // 校验sheet是否合法
        if (sheet == null) {
            return "不是合法的excel格式文件";
        }
            // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            return "解析Excel失败，在第一行没有读取到任何数据！";
        }

        int rowStart = firstRowNum + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        Map<String,Object> entities = new HashMap<>();
        List<Long> customerIds = new ArrayList<>() ;
        Long foodMaterialId = null ;
        Long supplierId = null ;
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                for (EntityDefine.EntityDesc entityDesc : CustomerSupplierDefine) {
                    String value = null ;
                    if (entityDesc.isCheck() && value == null) {
                        return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                    }
                    if (value != null) value = value.trim();
                    if (entityDesc.setField.equals("CustomerId")) {
                        Long result = customers.get(value);
                        if (result != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                        customerIds.add(result) ;
                    } else if (entityDesc.setField.equals("FoodMaterialId")) {
                        foodMaterialId = foods.get(value);
                        if (foodMaterialId != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    } else if (entityDesc.setField.equals("SupplierId")) {
                        supplierId = suppliers.get(value);
                        if (supplierId != null) return rowNum + "行：【" + entityDesc.getDesc() + "】输入错误";
                    }
                }
        }
        entities.put("customerId",customerIds) ;
        entities.put("foodMaterialId",foodMaterialId) ;
        entities.put("supplierId",supplierId) ;
        service.editCustomerFoodMaterialSupplier(entities) ;
        return null ;
    }

    public void downloadProductionCapacityExcel(ProcessBillDto dto, String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;

        List<ProcessBillDetail> data = stockDao.selectTotalProcess(dto) ;
        //构建每行的数据内容
        int rowNum = 4;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginStockDate() + "至" + dto.getEndStockDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(4).setCellValue(sheet.getRow(1).getCell(4).getStringCellValue() + dto.getProductionLineName());
            sheet.getRow(2).getCell(0).setCellValue(sheet.getRow(2).getCell(0).getStringCellValue() + dto.getFoodMaterialTypeName());
            for (ProcessBillDetail detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,ProductionCapacityDefine);
            }
            Row row = sheet.createRow(rowNum++);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    public void downloadProcessPlanExcel(PurchasePlanDto dto, String templateName, OutputStream out) {
        Workbook workbook = getWorkbook(templateName) ;

        List<ProcessBillDetail> data = stockDao.selectProcessPlan(dto) ;
        //构建每行的数据内容
        int rowNum = 3;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            String date = dto.getBeginDistributionDate() + "至" + dto.getEndDistributionDate() ;
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + date);
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getFoodMaterialTypeName());
            for (ProcessBillDetail detail : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(detail, row,ProcessPlanDefine);
            }
            Row row = sheet.createRow(rowNum++);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e) ;
        } finally {
            FileTools.close(workbook);
            FileTools.close(out);
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public String uploadSupplierExcel(Workbook workbook) {
        if (workbook == null) return "不是合法的excel格式文件" ;
        // 解析sheet
        List<Supplier> suppliers = new ArrayList<>();
        Long defaultId = new Long(-1) ;
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                return "解析Excel失败，在第一行没有读取到任何数据！";
            }
            // 解析每一行的数据，构造数据对象
            StringBuilder sb = new StringBuilder();
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                Supplier entity = new Supplier();
                sb.setLength(0);
                for (EntityDefine.EntityDesc entityDesc : SupplierDefine) {
                    String value = null;
                    Long id = null ;
                    if (entityDesc.getSetField().equals("Tel")) {
                        value = convertCellValueToString(row.getCell(entityDesc.getPos()), 1);
                    } else {
                        value = convertCellValueToString(row.getCell(entityDesc.getPos()),0) ;
                    }
                    if (entityDesc.isCheck() && value == null) {
                        return rowNum + "行：【" + entityDesc.getDesc() + "】不能为空" ;
                    }
                    if (value != null) value = value.trim();
                    setter(entity, entityDesc, value);
                }
                entity.setId(worker.nextId());
                suppliers.add(entity) ;
            }
        }
        purchaseDao.batchInsertSupplier(suppliers) ;
        return null ;
    }

    public void downloadFoodProductExcel(FoodProductDto dto,String templateName, OutputStream out,OrderService orderService) {
        Workbook workbook = getWorkbook(templateName) ;
        List<FoodProduct> data = orderService.foodProduct(dto) ;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(1).getCell(0).setCellValue(sheet.getRow(1).getCell(0).getStringCellValue() + dto.getBeginEatDate());
            sheet.getRow(1).getCell(3).setCellValue(sheet.getRow(1).getCell(3).getStringCellValue() + dto.getMealsTypeName());
            //构建每行的数据内容
            int rowNum = 3;
            for (FoodProduct fp : data) {
                //输出行数据
                Row row = sheet.createRow(rowNum++);
                convertDataToRow(fp, row,FoodProductDefine);
            }
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            FileTools.close(out);
            FileTools.close(workbook);
        }
    }

    public long nextId() {
        return worker.nextId() ;
    }
}
